package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.bean.UserLoginBean;
import com.bean.DotBean;
import com.bean.UserBean;
import com.db.JdbcUtil;
import com.exception.DBException;
import com.exception.UserException;

public class UserDao
{
	private Connection con = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	private ArrayList<UserBean> list = new ArrayList<UserBean>();

	/**
	 * add a new user
	 * 
	 * @param userBean
	 * @return mark
	 * @throws DBException
	 * @throws UserException
	 */
	public void addUser(UserBean userBean, UserLoginBean userLoginBean,
			DotBean pro) throws DBException, UserException
	{

		String sqluser = "insert into lab_user(userID,firstname,lastname,gender,age,email,phone,remark,date)"
				+ "values(" + "'"+ userBean.getUserID()+ "'" + ","
				+ "'" + userBean.getFirstname()+ "'" + ","
				+ "'" + userBean.getLastname() + "'" + "," 
				+ "'" + userBean.getGender() + "'" + ","
				+ "'" + userBean.getAge() + "'" + ","
				+ "'" + userBean.getEmail() + "'" + "," 
				+ "'" + userBean.getPhone() + "'" + ","
				+ "'" + userBean.getRemark()+ "'" + ","
				+ "curdate())";
		String sqlUserLogin = "insert into lab_userlogin(userID,username,password)"
				+ "values(" + "'" + userLoginBean.getUserID() + "'" + ","
				+ "'" + userLoginBean.getUsername() + "'" + ","
				+ "'" + userLoginBean.getPassword() + "')";

		String sqlDot = "insert into lab_provisional(userID,context)"
				+ "values(" + "'" + pro.getUserID() + "'" + "," + "'"
				+ pro.getContext() + "')";

		con = JdbcUtil.getConnection();

		try
		{
			con.setAutoCommit(false);
			Statement stmt = con.createStatement();
			stmt.addBatch(sqluser);
			stmt.addBatch(sqlUserLogin);
			stmt.addBatch(sqlDot);
			stmt.executeBatch();
			con.commit();

		}
		catch (SQLException e)
		{
			try
			{
				con.rollback();
				con.setAutoCommit(true);
			}
			catch (SQLException e1)
			{
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new UserException("Fail to add user");
		}

		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
	}

	/**
	 * update user info
	 * 
	 * @param userBean
	 * @return mark
	 * @throws UserException
	 * @throws DBException
	 */
	public void updateUser(UserBean userBean) throws UserException, DBException
	{
		String sql = "update lab_user set firstname=?,lastname=?,gender=?,age=?,email=?,"
				+ "phone=?,remark=? where userID=?";

		con = JdbcUtil.getConnection();
		try
		{
			ps = con.prepareStatement(sql);
			ps.setString(1, userBean.getFirstname());
			ps.setString(2, userBean.getLastname());
			ps.setString(3, userBean.getGender());
			ps.setString(4, userBean.getAge());
			ps.setString(5, userBean.getEmail());
			ps.setString(6, userBean.getPhone());
			ps.setString(7, userBean.getRemark());
			ps.setString(8, userBean.getUserID());
			ps.executeUpdate();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			throw new UserException("Fail to update user info");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}

	}

	/**
	 * delete user
	 * 
	 * @param userBean
	 * @return mark
	 * @throws UserException
	 * @throws DBException
	 */
	public void deleteUser(UserBean userBean) throws UserException, DBException
	{
		String sql = "delete from lab_user where userID=?";
		String sql_in = "select barcode from lab_record b join lab_user a on a.userID = b.userID where b.backDate is null and a.userID='"+userBean.getUserID()+"'";
		con = JdbcUtil.getConnection();
		try
		{
			//some of Instrument that this user is keeping
			Statement stmt = con.createStatement();
			rs = stmt.executeQuery(sql_in);
			while(rs.next())
			{
				throw new UserException("some of Instrument that this user is keeping,<br>barcode is " + rs.getString(1) + ", please check!");
			}
			try
			{
				ps = con.prepareStatement(sql);
				ps.setString(1, userBean.getUserID());
				ps.executeUpdate();
			}
			catch (Exception e)
			{
				throw new UserException("Fail to delete user");
			}
		}
		catch (SQLException e)
		{
			throw new UserException("Fail to check whether some instrumets are being keep when delete user");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
	}

	public void getUserDetails(UserBean userBean) throws DBException,
			UserException
	{
		String sql = "select * from lab_user where userID =?";
		con = JdbcUtil.getConnection();
		try
		{
			ps = con.prepareStatement(sql);
			ps.setString(1, userBean.getUserID());
			rs = ps.executeQuery();
			while (rs.next())
			{
				userBean.setUserID(rs.getString("userID"));
				userBean.setFirstname(rs.getString("firstname"));
				userBean.setLastname(rs.getString("lastname"));
				userBean.setGender(rs.getString("gender"));
				userBean.setAge(rs.getString("age"));
				userBean.setEmail(rs.getString("email"));
				userBean.setPhone(rs.getString("phone"));
				userBean.setRemark(rs.getString("remark"));
				userBean.setDate(rs.getDate("date"));
			}
		}
		catch (Exception e)
		{
			throw new UserException("Fail to get user details");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
	}

	/**
	 * query users by user ID
	 * 
	 * @param userBean
	 * @return list
	 * @throws DBException
	 * @throws UserException
	 */
	public ArrayList<UserBean> getUsersByID(UserBean userBean)
			throws DBException, UserException
	{

		String sql = "select userID,firstname,lastname,email from lab_user"
				+ " where userID like '%" + userBean.getUserID() + "%'";
		con = JdbcUtil.getConnection();
		try
		{
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next())
			{
				userBean = new UserBean();
				userBean.setUserID(rs.getString("userID"));
				userBean.setFirstname(rs.getString("firstname"));
				userBean.setLastname(rs.getString("lastname"));
				userBean.setEmail(rs.getString("email"));
				list.add(userBean);
			}
		}
		catch (Exception e)
		{
			throw new UserException("Fail to query");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
		return list;
	}

	public boolean ifUserExist(UserBean userBean) throws DBException,
			UserException
	{

		String sql = "select userID from lab_user" + " where userID = '"
				+ userBean.getUserID() + "'";
		boolean ifExit = false;
		con = JdbcUtil.getConnection();
		try
		{
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next())
			{
				ifExit = true;
			}
		}
		catch (Exception e)
		{
			throw new UserException("Fail to query");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
		return ifExit;
	}

	/**
	 * query users by user firstname
	 * 
	 * @param userBean
	 * @return list
	 * @throws UserException
	 * @throws DBException
	 */
	public ArrayList<UserBean> getUserByFirstname(UserBean userBean)
			throws UserException, DBException
	{

		String sql = "select userID,firstname,lastname,email from lab_user"
				+ " where firstname like '%" + userBean.getFirstname() + "%'";
		con = JdbcUtil.getConnection();
		try
		{
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next())
			{
				userBean = new UserBean();
				userBean.setUserID(rs.getString("userID"));
				userBean.setFirstname(rs.getString("firstname"));
				userBean.setLastname(rs.getString("lastname"));
				userBean.setEmail(rs.getString("email"));

				list.add(userBean);
			}
		}
		catch (Exception e)
		{
			throw new UserException("Fail to query by firstname");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
		return list;
	}

	/**
	 * query users by user lastname
	 * 
	 * @param userBean
	 * @return list
	 * @throws UserException
	 * @throws DBException
	 */
	public ArrayList<UserBean> getUserByLastName(UserBean userBean)
			throws UserException, DBException
	{

		String sql = "select userID,firstname,lastname,email from lab_user"
				+ " where lastname like '%" + userBean.getLastname() + "%'";
		con = JdbcUtil.getConnection();
		try
		{
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next())
			{
				userBean = new UserBean();
				userBean.setUserID(rs.getString("userID"));
				userBean.setFirstname(rs.getString("firstname"));
				userBean.setLastname(rs.getString("lastname"));
				userBean.setEmail(rs.getString("email"));
				list.add(userBean);
			}
		}
		catch (Exception e)
		{
			throw new UserException("Fail to query by surame");
		}

		finally
		{
			JdbcUtil.free(rs, ps, con);
		}

		return list;
	}

}
